﻿/*
* This software is the confidential and proprietary
* information of NetBay
* Such confidential information should not be disclosed
* NetBay copyright(c) 2011
*/
#region Using
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Linq;
using NetBay.Core.BusinessEntities.Workflow;
using NetBay.Core.DataAccess;
using NetBay.Core.Extensions;
using NetBay.Core.Log;

#endregion

namespace Rio.Flow.DataAccess
{
    public class DA_WorkflowInstance
    {
        #region Enum
        /// <summary>
        /// List of DB functions to access data
        /// </summary>
        private enum DbFunction
        {
            [DescriptionAttribute("P_WKF_INST_Add")]
            AddInstance,
            [DescriptionAttribute("P_WKF_INST_Get")]
            GetInstance,
            [DescriptionAttribute("P_WKF_INST_Get_Child")]
            GetChildInstance,
            [DescriptionAttribute("P_WKF_INST_Get_Parent")]
            GetParentInstance,
            [DescriptionAttribute("P_WKF_INST_Get_ByDefId")]
            GetInstancesByDefinitionId,
            [DescriptionAttribute("P_WKF_INST_LINK_Add")]
            AddInstanceLink,
            [DescriptionAttribute("P_WKF_INST_REC_Get_ById")]
            GetInstanceReceivers,
            [DescriptionAttribute("P_WKF_INST_REC_Add")]
            AddReceivers,
            [DescriptionAttribute("P_WKF_INST_REC_Add_ById")]
            AddReceiversById,
            [DescriptionAttribute("P_WKF_INST_REC_Upt_Action")]
            UpdateReceiversAction,
            [DescriptionAttribute("P_WKF_INST_REC_Upt_Ended")]
            UpdateReceiversToEnded,
            [DescriptionAttribute("P_WKF_INST_REC_Get_Action")]
            GetLastReceiversAction,
            [DescriptionAttribute("P_WKF_INST_DOC_STATE_Get")]
            GetDocumentState,
            [DescriptionAttribute("P_WKF_INST_DOC_STATE_ByUsrInst")]
            GetDocumentStateByUserInstance,
            [DescriptionAttribute("P_WKF_INST_Upd")]
            UpdateInstance,
            [DescriptionAttribute("P_WKF_INST_USR_Del")]
            DeleteUserInstance,
            [DescriptionAttribute("P_WKF_INST_Del")]
            DeleteInstance,
            [DescriptionAttribute("P_WKF_INST_Remove")]
            RemoveInstance,
            [DescriptionAttribute("P_WKF_INST_USR_Get")]
            GetUserInstances,
            [DescriptionAttribute("P_WKF_INST_Get_ByFldTypeId")]
            GetInstancesByFldTypeId,
            [DescriptionAttribute("P_WKF_INST_Get_ByRecId")]
            GetInstancesByReceiverId,
            [DescriptionAttribute("P_WKF_INST_Get_BySenderId")]
            GetInstancesBySenderId,
            [DescriptionAttribute("P_WKF_INST_Cnt_ByRecId")]
            GetInstancesCountByReceiverId,
            [DescriptionAttribute("P_WKF_INST_Cnt_BySenderId")]
            GetInstancesCountBySenderId,
            [DescriptionAttribute("P_WKF_INST_DOC_STATE_Add")]
            AddDocumentState,
            [DescriptionAttribute("P_WKF_DOC_STATE_Upd_ById")]
            UpdateInstanceDocStateById,
            [DescriptionAttribute("P_WKF_DOC_STATE_Upd_ByInst")]
            UpdateInstanceDocStateByInst,
            [DescriptionAttribute("P_WKF_HISTO_Get_ById")]
            GetHistoById,
            [DescriptionAttribute("P_WKF_HISTO_Get_ByDocId")]
            GetHistoByDocId,
            [DescriptionAttribute("P_WKF_INST_Get_ByFld")]
            GetInstancesByFolderId,
            [DescriptionAttribute("P_WKF_INST_Get_ByUserId")]
            GetInstancesByUserId,
            [DescriptionAttribute("P_WKF_INST_READ_Upd")]
            UpdateInstanceRead,
            [DescriptionAttribute("P_WKF_INST_USR_READ_Upd")]
            UpdateUserInstanceRead,
            [DescriptionAttribute("P_WKF_INST_Upd_Ended")]
            UpdateInstanceToEnd,
            [DescriptionAttribute("P_WKF_GRP_INST_Upd_Ended")]
            UpdateGroupInstanceToEnd,
            [DescriptionAttribute("P_WKF_INST_USR_Del_ById")]
            UpdateAllInstanceUserToEnd,
            [DescriptionAttribute("P_WKF_DOC_Get_ByInstId")]
            GetWorkflowDocumentsByInstance,
            [DescriptionAttribute("P_WKF_DOC_Get_ByUserInstId")]
            GetWorkflowDocumentsByUserInstance,
            [DescriptionAttribute("P_WKF_HISTO_Add")]
            AddHistorical,
            [DescriptionAttribute("P_WKF_HISTO_Add_ByInst")]
            AddHistoricalByInstance,
            [DescriptionAttribute("P_DOCUMENT_WKF_INST_GET")]
            GetInstancesIdentifiers,
            [DescriptionAttribute("P_WKF_INST_Get_NotRead")]
            GetInstancesNotReadByUserId,
            [DescriptionAttribute("P_WKF_INST_User_Doc_State_Add")]
            AddUserInstanceDocState,
            [DescriptionAttribute("P_WKF_INST_User_Doc_State_Upd")]
            UpdateUserInstanceDocState,
            [DescriptionAttribute("P_WKF_INST_DOC_STATE_GetByDocId")]
            GetUserInstanceDocStateByDocId,
        }
        #endregion

        #region Functions

        /// <summary>
        /// Calls P_WKF_INSTANCE_Get stored procedure
        /// </summary>
        public static IDataReader GetWorkflowInstance(long wkfInstanceId, long userId, bool wkfInstanceIsRead = false, bool wkfUserInstanceIsRead = false)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstance.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstance.szGetDescription());
            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            
            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Get_Child stored procedure
        /// </summary>
        public static IDataReader GetChildWorkflowInstance(long wkfParentInstanceId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetChildInstance.szGetDescription());
            LogManager.Instance().LogDebug("(wkfParentInstanceId<" + wkfParentInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetChildInstance.szGetDescription());
            db.AddInParameter(dbCommand, "p_lWkfParentId", DbType.Int64, wkfParentInstanceId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Get_Parent stored procedure
        /// </summary>
        public static IDataReader GetParentWorkflowInstance(long wkfInstanceId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetParentInstance.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetParentInstance.szGetDescription());
            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_Get_ByDefId stored procedure
        /// </summary>
        /// <param name="wkfDefinitionId">The WKF definition id.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesByDefinitionId(long wkfDefinitionId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesByDefinitionId.szGetDescription());
            LogManager.Instance().LogDebug("(wkfDefinitionId<" + wkfDefinitionId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesByDefinitionId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lWkfDefId", DbType.Int64, wkfDefinitionId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_Get_ByFld stored procedure
        /// </summary>
        /// <param name="folderTypeId">The folder type id.</param>
        /// <param name="folderId">The folder id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="isFolderTypeAdmin">The is folder type admin.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesByFolderId(long folderTypeId, long folderId, long userId, bool? isFolderTypeAdmin = false)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesByFolderId.szGetDescription());
            LogManager.Instance().LogDebug("(folderTypeId<" + folderTypeId + ">folderId<" + folderId + ">userId<" + userId + ">isFolderTypeAdmin<" + isFolderTypeAdmin.ToString() + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesByFolderId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, folderId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_lIsAdmin", DbType.Int32, Convert.ToInt32(isFolderTypeAdmin));
          
            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Get_BySenderId stored procedure
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="folderTypeId">The folder type id.</param>
        /// <param name="completedInstance">The completed instance.</param>
        public static IDataReader GetWorkflowInstancesBySenderId(long userId, long folderTypeId, bool completedInstance)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesBySenderId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId + ">folderTypeId<" + folderTypeId + ">completedInstance<" + completedInstance + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesBySenderId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeId);
            db.AddInParameter(dbCommand, "p_iWkfInstanceEnded", DbType.Int32, Convert.ToInt32(completedInstance));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Get_ByRecId stored procedure
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="folderTypeId">The folder type id.</param>
        /// <param name="completedInstance">The completed instance.</param>
        public static IDataReader GetWorkflowInstancesByReceiverId(long userId, long folderTypeId, bool completedInstance)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesByReceiverId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId + ">folderTypeId<" + folderTypeId + ">completedInstance<" + completedInstance + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesByReceiverId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeId);
            db.AddInParameter(dbCommand, "p_iWkfInstanceEnded", DbType.Int32, Convert.ToInt32(completedInstance));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the workflow instances by FLD type id.
        /// </summary>
        /// <param name="folderTypeId">The folder type id.</param>
        /// <param name="completedInstance">The completed instance.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesByFldTypeId(long folderTypeId, bool completedInstance)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesByFldTypeId.szGetDescription());
            LogManager.Instance().LogDebug("(folderTypeId<" + folderTypeId + ">completedInstance<" + completedInstance.ToString() + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesByFldTypeId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeId);
            db.AddInParameter(dbCommand, "p_iWkfInstanceEnded", DbType.Int32, Convert.ToInt32(completedInstance));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Cnt_BySenderId stored procedure
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="completedInstance">if set to <c>true</c> [completed instance].</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesCountBySenderId(long userId, bool completedInstance)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesCountBySenderId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId.ToString() + ">completedInstance<" + completedInstance.ToString() + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesCountBySenderId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_iEnded", DbType.Int32, Convert.ToInt32(completedInstance));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Cnt_ByRecId stored procedure
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="completedInstance">if set to <c>true</c> [completed instance].</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesCountByReceiverId(long userId, bool completedInstance)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesCountByReceiverId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId.ToString() + ">completedInstance<" + completedInstance.ToString() + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesCountByReceiverId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_iEnded", DbType.Int32, Convert.ToInt32(completedInstance));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the workflow instance not readed.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <returns></returns>
        public static long GetWorkflowInstanceNotRead(long userId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesNotReadByUserId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId + ">)");

            var db = DbHelper.DataBase;
            long result = 0;
            using (var dbCommand = db.GetNewCommand(DbFunction.GetInstancesNotReadByUserId.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        result = reader.GetLongValue("r_wkf_instance_id");
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_REC_Get_ById stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static string GetReceivers(long wkfInstanceId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstanceReceivers.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            string receivers = string.Empty;
            using (var dbCommand = db.GetNewCommand(DbFunction.GetInstanceReceivers.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        receivers += string.Format("{0};", reader["RECEIVERS"].ToString());
                    }
                }
            }
            return receivers;
        }

        /// <summary>
        /// Gets the document instances list.
        /// </summary>
        /// <param name="documentIdentifier">The document identifier.</param>
        /// <param name="deletedInstances">if set to <c>true</c> [deleted instances].</param>
        /// <returns></returns>
        public static List<long> GetDocumentInstancesList(long documentIdentifier, bool deletedInstances)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesIdentifiers.szGetDescription());
            LogManager.Instance().LogDebug("(documentIdentifier<" + documentIdentifier + ">deletedInstances<" + deletedInstances.ToString() + ">)");

            var db = DbHelper.DataBase;
            List<long> instances = new List<long>();
            using (var dbCommand = db.GetNewCommand(DbFunction.GetInstancesIdentifiers.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, documentIdentifier);
                db.AddInParameter(dbCommand, "p_lWkfIsEnded", DbType.Int32, Convert.ToInt32(deletedInstances));

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        instances.Add(reader.GetLongValue("r_wkf_instance_id"));
                    }
                }
            }
            return instances;
        }

        /// <summary>
        /// Calls P_WKF_INST_REC_Get_Action stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="rdNo">The rd no.</param>
        /// <returns></returns>
        public static long GetLastReceiversAction(long wkfInstanceId, long rguId, long? rdNo)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetLastReceiversAction.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            long receivers = -1;
            using (var dbCommand = db.GetNewCommand(DbFunction.GetLastReceiversAction.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                db.AddInParameter(dbCommand, "p_lRdNo", DbType.Int64, rdNo);
                db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        if (reader["t_ref_wkf_act_cond_id"] != DBNull.Value)
                        {
                            receivers = Int64.Parse(reader["t_ref_wkf_act_cond_id"].ToString());
                        }
                    }
                }
            }
            return receivers;
        }

        /// <summary>
        /// Adds the workflow instance.
        /// </summary>
        /// <param name="wkfDefinition">The WKF definition.</param>
        /// <param name="folderId">The folder id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="state">The state.</param>
        /// <param name="StartingCondition">The starting condition.</param>
        /// <param name="docId">The doc id.</param>
        /// <param name="ChronoId">The chrono id.</param>
        /// <param name="parentId">The parent id.</param>
        /// <param name="receiversGroupIds">The receivers group ids.</param>
        /// <returns>
        /// Return the instance id, or -1 if error
        /// </returns>
        public static long AddWorkflowInstance(WorkflowDefinition wkfDefinition, long folderId, long userId, WorkflowState state, long StartingCondition, List<long> docId = null, long? ChronoId = 0, long? parentId = null, List<long> receiversGroupIds = null)
        {

            LogManager.Instance().LogDebug("...");
            var db = DbHelper.DataBase;
            DbTransaction transaction = null;
            long newInstanceIdentifier = 0;

            using (DbConnection connection_db_b = db.CreateConnection())
            {
                try
                {
                    connection_db_b.Open();
                    transaction = connection_db_b.BeginTransaction();

                    //Add instance
                    var dbCommand = db.GetNewCommand(DbFunction.AddInstance.szGetDescription());
                    db.AddInParameter(dbCommand, "p_lWkfDefId", DbType.Int64, wkfDefinition.Identifier);
                    db.AddInParameter(dbCommand, "p_lTdNo", DbType.Int64, folderId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(state));
                    db.AddInParameter(dbCommand, "p_lWkfInstanceParentId", DbType.Int64, parentId);
                    db.AddInParameter(dbCommand, "p_sIdDocs", DbType.String, ConvertListToString(docId));
                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
                    db.AddInParameter(dbCommand, "p_lChronoId", DbType.Int64, ChronoId);
                    db.AddInParameter(dbCommand, "p_lWkfActCondId", DbType.Int64, Convert.ToInt32(StartingCondition));
                    using (IDataReader reader = db.ExecuteReader(dbCommand, transaction))
                    {
                        while (reader.Read())
                        {
                            newInstanceIdentifier = Convert.ToInt64(reader["INSTANCE_ID"]);
                        }
                    }

                    //Get Receivers
                    if (receiversGroupIds == null)
                    {
                        string receivers = string.Empty;
                        dbCommand = db.GetNewCommand(DbFunction.GetInstanceReceivers.szGetDescription());
                        db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                        using (IDataReader reader = db.ExecuteReader(dbCommand, transaction))
                        {
                            while (reader.Read())
                            {
                                receivers += string.Format("{0};", reader["RECEIVERS"].ToString());
                            }
                        }

                        //no receivers found!
                        if(string.IsNullOrEmpty(receivers.TrimEnd(';')))
                        {
                            LogManager.Instance().LogWarn("Could not start workflow, no receivers found!");
                            //Remove instance
                            if (newInstanceIdentifier > 0)
                            {
                                dbCommand = db.GetNewCommand(DbFunction.RemoveInstance.szGetDescription(), false);
                                db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                                db.ExecuteNonQuery(dbCommand, transaction);
                            }
                            transaction.Commit();
                            return 0;
                        }

                        //Add instance receivers
                        dbCommand = db.GetNewCommand(DbFunction.AddReceivers.szGetDescription(), false);
                        db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                        db.AddInParameter(dbCommand, "p_sReceivers", DbType.String, receivers);
                        db.ExecuteNonQuery(dbCommand, transaction);
                    }
                    else
                    {
                        //Add receivers 
                        foreach (var item in receiversGroupIds)
                        {
                            dbCommand = db.GetNewCommand(DbFunction.AddReceiversById.szGetDescription(), false);
                            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                            db.AddInParameter(dbCommand, "p_lRgid", DbType.Int64, item);
                            db.ExecuteNonQuery(dbCommand, transaction);
                        }
                    }

                    //
                    if (docId != null)
                    {

                        dbCommand = db.GetNewCommand(DbFunction.GetUserInstances.szGetDescription());
                        db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                        db.AddInParameter(dbCommand, "p_iDeleted", DbType.Int32, 0);

                        //Get user instances
                        List<long> userInstanceIds = new List<long>();
                        using (IDataReader reader = db.ExecuteReader(dbCommand, transaction))
                        {
                            while (reader.Read())
                            {
                                userInstanceIds.Add(Convert.ToInt64(reader.GetLongValue("r_wkf_instance_usr_id")));
                            }
                        }

                        //Get doc state instance
                        dbCommand = db.GetNewCommand(DbFunction.GetDocumentState.szGetDescription());
                        db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, newInstanceIdentifier);
                        List<long> docStateIds = new List<long>();
                        using (IDataReader reader = db.ExecuteReader(dbCommand, transaction))
                        {
                            while (reader.Read())
                            {
                                docStateIds.Add(Convert.ToInt64(reader.GetLongValue("r_wkf_doc_state_id")));
                            }
                        }

                        //Add user instance doc state
                        foreach (long userInstId in userInstanceIds)
                        {
                            foreach (long docStateId in docStateIds)
                            {
                                dbCommand = db.GetNewCommand(DbFunction.AddUserInstanceDocState.szGetDescription(), false);
                                db.AddInParameter(dbCommand, "p_lInstanceUsrId", DbType.Int64, userInstId);
                                db.AddInParameter(dbCommand, "p_lWkfDocStateId", DbType.Int64, docStateId);
                                db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64,
                                                  Convert.ToInt64(WorkflowState.None));
                                db.ExecuteNonQuery(dbCommand, transaction);
                            }
                        }

                    }


                    transaction.Commit();
                    dbCommand.Dispose();
                }
                catch (Exception ex)
                {
                    LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
                    if (transaction != null)
                    {
                        transaction.Rollback();
                        LogManager.Instance().LogInfo("Rollback() executed...");
                    }

                    return 0;
                }
            }

            return newInstanceIdentifier;
        }

        /// <summary>
        /// Calls P_WKF_INST_USR_Del stored procedure
        /// </summary>
        /// <param name="wkfUserInstanceId">The WKF user instance id.</param>
        /// <param name="docStateId">The doc state id.</param>
        /// <param name="newState">The new state.</param>
        /// <returns></returns>
        public static bool UpdateUserInstanceDocState(long wkfUserInstanceId, long? docStateId, WorkflowState newState)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.DeleteUserInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfUserInstanceId<" + wkfUserInstanceId + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateUserInstanceDocState.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lInstanceUsrId", DbType.Int64, wkfUserInstanceId);
                    if (docStateId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lWkfDocStateId", DbType.Int64, docStateId);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lWkfDocStateId", DbType.Int64, DBNull.Value);
                    }
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(newState));
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_LINK_Add stored procedure
        /// </summary>
        /// <param name="wkfInstanceIdParent">Workflow parent instance identifier</param>
        /// <param name="wkfInstanceIdChild">Workflow parent instance identifier</param>
        /// <returns>True if ok</returns>
        public static bool AddInstanceLink(long wkfInstanceIdParent, long wkfInstanceIdChild)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddInstanceLink.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceIdParent<" + wkfInstanceIdParent + "> wkfInstanceIdChild<" + wkfInstanceIdChild + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.AddInstanceLink.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceIdParent", DbType.Int64, wkfInstanceIdParent);
                    db.AddInParameter(dbCommand, "p_lWkfInstanceIdChild", DbType.Int64, wkfInstanceIdChild);

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_DOC_STATE_Get stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">Instance identifier</param>
        /// <returns></returns>
        public static IDataReader GetDocumentState(long wkfInstanceId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetDocumentState.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentState.szGetDescription());

            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_DOC_STATE_Get stored procedure
        /// </summary>
        /// <param name="userInstanceId">The user instance id.</param>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <returns></returns>
        public static IDataReader GetDocumentStateByUserInstance(long userInstanceId, long wkfInstanceId, long rguId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetDocumentStateByUserInstance.szGetDescription());
            LogManager.Instance().LogDebug("(userInstanceId<" + userInstanceId + ">wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentStateByUserInstance.szGetDescription());

            db.AddInParameter(dbCommand, "p_lWkfUserInstanceId", DbType.Int64, userInstanceId);
            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
            db.AddInParameter(dbCommand, "p_lRgu_id", DbType.Int64, rguId);
            
            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_DOC_STATE_Add stored procedure
        /// </summary>
        /// <param name="DocumentId">The document id.</param>
        /// <param name="wkfState">State of the WKF.</param>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static bool AddDocumentState(long DocumentId, WorkflowState wkfState, long wkfInstanceId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddDocumentState.szGetDescription());
                LogManager.Instance().LogDebug("(DocumentId<" + DocumentId + "> wkfState<" + wkfState.ToString() + ">wkfInstanceId<" + wkfInstanceId + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.AddDocumentState.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lRdNo", DbType.Int64, DocumentId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToSByte(wkfState));
                    db.AddInParameter(dbCommand, "p_lInstanceId", DbType.Int64, wkfInstanceId);

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Adds the user instances.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="receiversGroupIds">The receivers group ids.</param>
        /// <returns></returns>
        public static bool AddUserInstances(long wkfInstanceId, List<long> receiversGroupIds)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddReceiversById.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");
                var db = DbHelper.DataBase;

                //Add receivers 
                foreach (var item in receiversGroupIds)
                {
                    using (var dbCommand = db.GetNewCommand(DbFunction.AddReceiversById.szGetDescription(), false))
                    {
                        db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                        db.AddInParameter(dbCommand, "p_lRgid", DbType.Int64, item);
                        db.ExecuteNonQuery(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Adds the user instances.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="receivers">The receivers.</param>
        /// <returns></returns>
        public static bool AddUserInstances(long wkfInstanceId, string receivers)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddReceivers.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">receivers<" + receivers + ">)");
                var db = DbHelper.DataBase;

                //Add receivers 
                using (var dbCommand = db.GetNewCommand(DbFunction.AddReceivers.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_sReceivers", DbType.String, receivers);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Updates the receivers action.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguid">The rguid.</param>
        /// <param name="actionId">The action id.</param>
        /// <returns></returns>
        public static bool UpdateReceiversAction(long wkfInstanceId, long rguid, long actionId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateReceiversAction.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">rguid<" + rguid + ">actionId<" + actionId + ">)");
                var db = DbHelper.DataBase;

                //Add receivers 
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateReceiversAction.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguid);
                    db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, actionId);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Updates the receivers to ended action.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguid">The rguid.</param>
        /// <param name="isEnded">The is ended.</param>
        /// <returns></returns>
        public static bool UpdateReceiversToEnded(long wkfInstanceId, long rguid, bool isEnded)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateReceiversToEnded.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">rguid<" + rguid + ">isEnded<" + isEnded + ">)");
                var db = DbHelper.DataBase;

                //Add receivers 
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateReceiversToEnded.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguid);
                    db.AddInParameter(dbCommand, "p_lIsEnded", DbType.Int64, Convert.ToInt32(isEnded));
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INST_USR_Get stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="deletedInstanceUser">if set to <c>true</c> [deleted instance user].</param>
        /// <returns></returns>
        public static IDataReader GetUserInstances(long wkfInstanceId, bool deletedInstanceUser = false)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetUserInstances.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">deletedInstanceUser<" + deletedInstanceUser + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetUserInstances.szGetDescription());

            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
            db.AddInParameter(dbCommand, "p_iDeleted", DbType.Int32, Convert.ToInt32(deletedInstanceUser));
            
            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_DOC_STATE_Upd_ById stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="wkfDocStateId">Document state identifier</param>
        /// <param name="wkfState">New State</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="actionOrConditionId">The action or condition id.</param>
        /// <param name="comment">The comment.</param>
        /// <returns>
        /// True if ok
        /// </returns>
        public static bool UpdateDocumentStateById(long wkfInstanceId, long wkfDocStateId, WorkflowState wkfState, long? rguId, long? userId, long actionOrConditionId, string comment = "")
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateInstanceDocStateById.szGetDescription());
                LogManager.Instance().LogDebugFormat("(wkfInstanceId<{0}> wkfDocStateId<{1}> wkfState<{2}> rguId<{3}> userId<{4}> actionOrConditionId<{5}> comment<{6}>",
                    wkfInstanceId,
                    wkfDocStateId,
                    wkfState,
                    rguId,
                    userId,
                    actionOrConditionId,
                    comment);

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateInstanceDocStateById.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfDocStateId", DbType.Int64, wkfDocStateId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(wkfState));
                    db.AddInParameter(dbCommand, "p_sWkfDocStateComment", DbType.String, comment);
                    if (!rguId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }
                    if (!userId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId.Value);
                    }
                    db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, actionOrConditionId);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_DOC_STATE_Upd_ByInst stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="wkfState">State of the WKF.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="actionOrConditionId">The action or condition id.</param>
        /// <param name="comment">The comment.</param>
        /// <returns></returns>
        public static bool UpdateDocumentStateByInstanceId(long wkfInstanceId, WorkflowState wkfState, long? rguId, long? userId, long actionOrConditionId, string comment = "")
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateInstanceDocStateByInst.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + "> wkfState<" + wkfState.ToString() + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateInstanceDocStateByInst.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(wkfState));
                    if (!rguId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }
                    if (!userId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId.Value);
                    }
                    db.AddInParameter(dbCommand, "p_sWkfDocStateComment", DbType.String, comment);
                    db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, actionOrConditionId);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INSTANCE_Upd stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">Instance identifier</param>
        /// <param name="wkfState">New State</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="isEnded">if set to <c>true</c> [is ended].</param>
        /// <returns>
        /// True if ok
        /// </returns>
        public static bool UpdateInstance(long wkfInstanceId, WorkflowState wkfState, long? rguId, long? userId, bool isEnded = false)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + "> wkfState<" + wkfState.ToString() + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateInstance.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(wkfState));
                    db.AddInParameter(dbCommand, "p_iIsEnded", DbType.Int32, Convert.ToInt32(isEnded));
                    if (!rguId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }
                    if (!userId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId.Value);
                    }
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Updates the instance to readed.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static bool UpdateInstanceToReaded(long wkfInstanceId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateInstanceRead.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateInstanceRead.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Updates the user instance to readed.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="UserId">The user id.</param>
        /// <returns></returns>
        public static bool UpdateUserInstanceToReaded(long wkfInstanceId, long UserId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateUserInstanceRead.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + "> UserId<" + UserId.ToString() + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateUserInstanceRead.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, UserId);

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Updates the instance to ended.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="isEnded">The is ended.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <returns></returns>
        public static bool UpdateInstanceToEnded(long wkfInstanceId, bool isEnded, long? rguId, long? userId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.UpdateInstanceToEnd.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + "> isEnded<" + isEnded.ToString() + ">)");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.UpdateInstanceToEnd.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_iIsEnded", DbType.Int32, Convert.ToInt32(isEnded));
                    if (!rguId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }
                    if (!userId.HasValue)
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId.Value);
                    }

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INST_USR_Del stored procedure
        /// </summary>
        /// <param name="wkfUserInstanceId">The WKF user instance id.</param>
        /// <returns></returns>
        public static bool DeleteUserInstance(long wkfUserInstanceId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.DeleteUserInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfUserInstanceId<" + wkfUserInstanceId + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.DeleteUserInstance.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstUsrId", DbType.Int64, wkfUserInstanceId);
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INST_Del stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static bool DeleteInstance(long wkfInstanceId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.DeleteInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.DeleteInstance.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Removes the instance.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static bool RemoveInstance(long wkfInstanceId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.DeleteInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.RemoveInstance.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Calls P_WKF_INST_HISTO_Get stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <returns></returns>
        public static IDataReader GetHisto(long wkfInstanceId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetHistoById.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetHistoById.szGetDescription());

            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_HISTO_Get stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rdNo">The rd no.</param>
        /// <returns></returns>
        public static IDataReader GetHistoByDocId(long wkfInstanceId, long rdNo)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetHistoByDocId.szGetDescription());
            LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">rdNo<" + rdNo + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetHistoByDocId.szGetDescription());

            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
            db.AddInParameter(dbCommand, "p_lRdNo", DbType.Int64, rdNo);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_INST_Get_ByUserId stored procedure
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowInstancesByUserId(long userId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetInstancesByUserId.szGetDescription());
            LogManager.Instance().LogDebug("(userId<" + userId + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetInstancesByUserId.szGetDescription());

            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Restarts the workflow instance.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="newReceivers">The new receivers.</param>
        /// <returns></returns>
        public static bool RestartWorkflowInstance(long wkfInstanceId, string newReceivers)
        {
            LogManager.Instance().LogDebug("...");
            var db = DbHelper.DataBase;
            DbTransaction transaction = null;

            using (DbConnection connection_db_b = db.CreateConnection())
            {
                try
                {
                    connection_db_b.Open();
                    transaction = connection_db_b.BeginTransaction();

                    //Update document to initial state
                    var dbCommand = db.GetNewCommand(DbFunction.UpdateInstanceDocStateByInst.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(WorkflowState.None));
                    db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                     db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    db.AddInParameter(dbCommand, "p_sWkfDocStateComment", DbType.String, string.Empty);
                    db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, DBNull.Value);
                    db.ExecuteNonQuery(dbCommand);

                    //Delete instance 
                    dbCommand = db.GetNewCommand(DbFunction.UpdateAllInstanceUserToEnd.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.ExecuteNonQuery(dbCommand);

                    //Add instance receivers
                    dbCommand = db.GetNewCommand(DbFunction.AddReceivers.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_sReceivers", DbType.String, newReceivers);
                    db.ExecuteNonQuery(dbCommand, transaction);

                    //Update instance to initial state
                    dbCommand = db.GetNewCommand(DbFunction.UpdateInstance.szGetDescription(), false);
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(WorkflowState.Started));
                    db.AddInParameter(dbCommand, "p_iIsEnded", DbType.Int32, Convert.ToInt32(false));
                    db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, DBNull.Value);
                    db.ExecuteNonQuery(dbCommand);

                    //Commit
                    transaction.Commit();
                    dbCommand.Dispose();
                }
                catch (Exception ex)
                {
                    LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
                    if (transaction != null)
                    {
                        transaction.Rollback();
                        LogManager.Instance().LogInfo("Rollback() executed...");
                    }
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Gets the workflow document by folder.
        /// </summary>
        /// <param name="folderTypeID">The folder type ID.</param>
        /// <param name="folderId">The folder id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="wkfState">State of the WKF.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowDocumentsByInstance(long folderTypeID, long folderId, long rguId, long userId, long wkfInstanceId, WorkflowState wkfState)
        {
            LogManager.Instance().LogDebug("(folderTypeID<" + folderTypeID + ">folderId<" + folderId + ">rguId<" + rguId + ">userId<" + userId + ">wkfInstanceId<" + wkfInstanceId + ">wkfState<" + wkfState + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetWorkflowDocumentsByInstance.szGetDescription());

            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, folderId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeID);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId);
            db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);

            if (wkfState == WorkflowState.All)
            {
                db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, DBNull.Value);
            }
            else
            {
                db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(wkfState));
            }

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the workflow documents by user.
        /// </summary>
        /// <param name="folderTypeID">The folder type ID.</param>
        /// <param name="folderId">The folder id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="wkfUserInstanceId">The WKF user instance id.</param>
        /// <param name="wkfState">State of the WKF.</param>
        /// <returns></returns>
        public static IDataReader GetWorkflowDocumentsByUser(long folderTypeID, long folderId, long rguId, long userId, long wkfUserInstanceId, WorkflowState wkfState)
        {
            LogManager.Instance().LogDebug("(folderTypeID<" + folderTypeID + ">folderId<" + folderId + ">rguId<" + rguId + ">userId<" + userId + ">wkfUserInstanceId<" + wkfUserInstanceId + ">wkfState<" + wkfState + ">)");

            var db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.GetWorkflowDocumentsByUserInstance.szGetDescription());

            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, folderId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeID);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);
            db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId);
            db.AddInParameter(dbCommand, "p_lWkfInstanceUserId", DbType.Int64, wkfUserInstanceId);

            if (wkfState == WorkflowState.All)
            {
                db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, DBNull.Value);
            }
            else
            {
                db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(wkfState));
            }

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Calls P_WKF_HISTO_Add stored procedure
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="state">The state.</param>
        /// <param name="actionIdentifier">The action identifier.</param>
        /// <param name="documentIdentifier">The document identifier.</param>
        /// <returns></returns>
        public static bool AddHistorical(long wkfInstanceId, long? rguId, long userId,  WorkflowState state, long? actionIdentifier, long? documentIdentifier)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddHistorical.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">rguId<" + rguId + ">userId<" + userId + ">state<" + state + ">actionIdentifier<" + actionIdentifier + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.AddHistorical.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(state));

                    if (!rguId.HasValue || rguId.Value == 0)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }

                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);

                    if (!actionIdentifier.HasValue || actionIdentifier.Value == 0)
                    {
                        db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, actionIdentifier.Value);
                    }

                    if (!documentIdentifier.HasValue || documentIdentifier.Value == 0)
                    {
                        db.AddInParameter(dbCommand, "p_lRdNo", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRdNo", DbType.Int64, documentIdentifier.Value);
                    }
                    

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Adds the historical.
        /// </summary>
        /// <param name="wkfInstanceId">The WKF instance id.</param>
        /// <param name="rguId">The rgu id.</param>
        /// <param name="userId">The user id.</param>
        /// <param name="state">The state.</param>
        /// <param name="actionIdentifier">The action identifier.</param>
        /// <returns></returns>
        public static bool AddHistoricalForAllDocuments(long wkfInstanceId, long? rguId, long userId,  WorkflowState state, long? actionIdentifier)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.AddHistoricalByInstance.szGetDescription());
                LogManager.Instance().LogDebug("(wkfInstanceId<" + wkfInstanceId + ">rguId<" + rguId + ">userId<" + userId + ">state<" + state + ">actionIdentifier<" + actionIdentifier + ">");

                var db = DbHelper.DataBase;
                using (var dbCommand = db.GetNewCommand(DbFunction.AddHistoricalByInstance.szGetDescription(), false))
                {
                    db.AddInParameter(dbCommand, "p_lWkfInstanceId", DbType.Int64, wkfInstanceId);
                    db.AddInParameter(dbCommand, "p_lWkfStateId", DbType.Int64, Convert.ToInt64(state));

                    if (!rguId.HasValue || rguId.Value == 0)
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lRguId", DbType.Int64, rguId.Value);
                    }

                    db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, userId);

                    if (!actionIdentifier.HasValue || actionIdentifier.Value == 0)
                    {
                        db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, DBNull.Value);
                    }
                    else
                    {
                        db.AddInParameter(dbCommand, "p_lActionId", DbType.Int64, actionIdentifier.Value);
                    }
                   

                    db.ExecuteNonQuery(dbCommand);
                }
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Gets the workflow instance by documents id.
        /// </summary>
        /// <param name="documentId">The document id.</param>
        /// <returns></returns>
        public static List<long> GetUserInstanceDocStateByDocumentsId(long documentId)
        {
            List<long> result = new List<long>();
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", DbFunction.GetUserInstanceDocStateByDocId.szGetDescription());
            var db = DbHelper.DataBase;
            using (var dbCommand = db.GetNewCommand(DbFunction.GetUserInstanceDocStateByDocId.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lrdNo", DbType.Int64, documentId);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        if (reader["r_wkf_doc_state_id"] != DBNull.Value)
                        {
                            result.Add(Int64.Parse(reader["r_wkf_doc_state_id"].ToString()));
                        }
                    }
                }
            }
            return result;
        }


        /// <summary>
        /// Converts the list to string.
        /// </summary>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        private static string ConvertListToString(List<long> list)
        {
            string result = string.Empty;
            if (list != null)
            {
                return list.Aggregate(result, (current, item) => current + (item + ";"));
            }
            return null;
        }

        #endregion
    }
}